﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DRHotel_v1.Classes.DBManagers
{
    class RoomAvaAndBookingDBManager
    {
        public static ArrayList GetAvaRooms(string roomType, string bedType, bool smoking)//need to improve: datetime, reserved time
        {
            ArrayList rooms = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Room WHERE roomType=@roomType AND bedType=@bedType AND smoking=@smoking AND roomStatus='Vacant' "
                    + "OR roomType=@roomType AND bedType=@bedType AND smoking=@smoking AND roomStatus='Clean(V)' ";
                cmd.Parameters.AddWithValue("@roomType", roomType);
                cmd.Parameters.AddWithValue("@bedType", bedType);
                cmd.Parameters.AddWithValue("@smoking", smoking);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room rm = new Room();
                    rm.roomID = Convert.ToInt32(dr["roomID"]);
                    rm.roomRates = Convert.ToDouble(dr["roomRates"]);
                    rm.roomStatus = Convert.ToString(dr["roomStatus"]);
                    rm.capacity = Convert.ToInt32(dr["capacity"]);
                    rm.roomType = Convert.ToString(dr["roomType"]);
                    rm.smoking = Convert.ToBoolean(dr["smoking"]);
                    rm.remark = Convert.ToString(dr["remark"]);
                    rm.bedType = Convert.ToString(dr["bedType"]);

                    rooms.Add(rm);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rooms;
        }

        public static ArrayList GetRoomType()
        {
            ArrayList roomTypes = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT DISTINCT roomType FROM Room";

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string rmtp = (string)dr["roomType"];
                    roomTypes.Add(rmtp);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomTypes;
        }

        public static ArrayList GetRoomTypeByBedType(string bedType)
        {
            ArrayList roomTypes = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT DISTINCT roomType FROM Room where bedType = @bedType";
                cmd.Parameters.AddWithValue("@bedType", bedType);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string rmtp = (string)dr["roomType"];
                    roomTypes.Add(rmtp);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomTypes;
        }

        public static ArrayList GetBedType()
        {
            ArrayList roomTypes = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT DISTINCT bedType FROM Room ";

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string rmtp = (string)dr["bedType"];
                    roomTypes.Add(rmtp);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomTypes;
        }

        public static ArrayList GetBedTypeByRoomType(string roomType)
        {
            ArrayList roomTypes = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT DISTINCT bedType FROM Room where roomType = @roomType";
                cmd.Parameters.AddWithValue("@roomType", roomType);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string rmtp = (string)dr["bedType"];
                    roomTypes.Add(rmtp);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return roomTypes;
        }

        public static int InsertReservation(Reservation res)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Reservation(cusIC,roomID,numOfAdult,numOfChildren,checkInDateTime,checkOutDateTime,remark,recordDateTime,resStatus) "
                    + "VALUES(@cusIC,@roomID,@numOfAdult,@numOfChildren,@checkInDT,@checkOutDT,@remark,@recordDT,@resStatus)";

                comm.Parameters.AddWithValue("@cusIC", res.cusIC);
                comm.Parameters.AddWithValue("@roomID", res.roomID);
                comm.Parameters.AddWithValue("@numOfAdult", res.numOfAdult);
                comm.Parameters.AddWithValue("@numOfChildren", res.numOfChildren);
                comm.Parameters.AddWithValue("@checkInDT", res.checkInDateTime);
                comm.Parameters.AddWithValue("@checkOutDT", res.checkOutDateTime);
                comm.Parameters.AddWithValue("@remark", res.remark);
                comm.Parameters.AddWithValue("@recordDT", res.recordDateTime);
                comm.Parameters.AddWithValue("@resStatus", res.resStatus);

                rowsInserted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static int InsertCustomer(Customer cus)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Customer(cusIC,ICType,firstName,lastName,gender,dob,country,phoneNo,email,mailingAddress,postCode,cusType) "
                    + "VALUES(@cusIC,@ICType,@firstName,@lastName,@gender,@dob,@country,@phoneNo,@email,@mailingAddress,@postCode,@cusType)";

                comm.Parameters.AddWithValue("@cusIC", cus.cusIC);
                comm.Parameters.AddWithValue("@ICType", cus.ICType);
                comm.Parameters.AddWithValue("@firstName", cus.firstName);
                comm.Parameters.AddWithValue("@lastName", cus.lastName);
                comm.Parameters.AddWithValue("@gender", cus.gender);
                comm.Parameters.AddWithValue("@dob", cus.dob);
                comm.Parameters.AddWithValue("@country", cus.country);
                comm.Parameters.AddWithValue("@phoneNo", cus.phoneNo);
                comm.Parameters.AddWithValue("@email", cus.email);
                comm.Parameters.AddWithValue("@mailingAddress", cus.mailingAddress);
                comm.Parameters.AddWithValue("@postCode", cus.postCode);
                comm.Parameters.AddWithValue("@cusType", cus.cusType);

                rowsInserted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static ArrayList GetReservationByStatus(string status)
        {
            ArrayList reservations = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Reservation where resStatus = @status";
                cmd.Parameters.AddWithValue("@status", status);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Reservation r = new Reservation();
                    r.resNum = Convert.ToInt32(dr["resNum"]);
                    r.cusIC = Convert.ToString(dr["cusIC"]);
                    r.roomID = Convert.ToInt16(dr["roomID"]);
                    r.numOfAdult = Convert.ToInt16(dr["numOfAdult"]);
                    r.numOfChildren = Convert.ToInt16(dr["numOfChildren"]);
                    r.checkInDateTime = Convert.ToDateTime(dr["checkInDateTime"]);
                    r.checkOutDateTime = Convert.ToDateTime(dr["checkOutDateTime"]);
                    r.remark = Convert.ToString(dr["remark"]);
                    r.recordDateTime = Convert.ToDateTime(dr["recordDateTime"]);
                    r.resStatus = Convert.ToString(dr["resStatus"]);
                    reservations.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return reservations;
        }

        public static ArrayList GetReservationByRmIDStatus(string roomID, string status)
        {
            ArrayList reservations = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Reservation where roomID = @roomID and resStatus = @status";
                cmd.Parameters.AddWithValue("@roomID", roomID);
                cmd.Parameters.AddWithValue("@status", status);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Reservation r = new Reservation();
                    r.resNum = Convert.ToInt32(dr["resNum"]);
                    r.cusIC = Convert.ToString(dr["cusIC"]);
                    r.roomID = Convert.ToInt16(dr["roomID"]);
                    r.numOfAdult = Convert.ToInt16(dr["numOfAdult"]);
                    r.numOfChildren = Convert.ToInt16(dr["numOfChildren"]);
                    r.checkInDateTime = Convert.ToDateTime(dr["checkInDateTime"]);
                    r.checkOutDateTime = Convert.ToDateTime(dr["checkOutDateTime"]);
                    r.remark = Convert.ToString(dr["remark"]);
                    r.recordDateTime = Convert.ToDateTime(dr["recordDateTime"]);
                    r.resStatus = Convert.ToString(dr["resStatus"]);
                    reservations.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return reservations;
        }

        public static ArrayList GetAllReservations()
        {
            ArrayList reservations = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Reservation";

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Reservation r = new Reservation();
                    r.resNum = Convert.ToInt32(dr["resNum"]);
                    r.cusIC = Convert.ToString(dr["cusIC"]);
                    r.roomID = Convert.ToInt16(dr["roomID"]);
                    r.numOfAdult = Convert.ToInt16(dr["numOfAdult"]);
                    r.numOfChildren = Convert.ToInt16(dr["numOfChildren"]);
                    r.checkInDateTime = Convert.ToDateTime(dr["checkInDateTime"]);
                    r.checkOutDateTime = Convert.ToDateTime(dr["checkOutDateTime"]);
                    r.remark = Convert.ToString(dr["remark"]);
                    r.recordDateTime = Convert.ToDateTime(dr["recordDateTime"]);
                    r.resStatus = Convert.ToString(dr["resStatus"]);
                    reservations.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return reservations;
        }

        public static bool SearchExistingCus(string IC)//search if the customer is existing in the database. return true if not existing
        {
            bool rowsFinded = false;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Customer WHERE cusIC = @cusIC";
                cmd.Parameters.AddWithValue("@cusIC", IC);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    rowsFinded=true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsFinded;
        }

        public static ArrayList SearchCustomer(string IC, string ICType, string firstName, string lastName, string cusType)
        {
            ArrayList cusList = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Customer "+
                    "WHERE cusIC = @cusIC AND ICType = @ICType AND cusType = @cusType " +
                    "OR firstName = @firstName AND ICType = @ICType AND cusType = @cusType " +
                    "OR lastName = @lastName AND ICType = @ICType AND cusType = @cusType " +
                    "OR firstName = @firstName AND lastName = @lastName AND ICType = @ICType AND cusType = @cusType " ;

                cmd.Parameters.AddWithValue("@cusIC", IC);
                cmd.Parameters.AddWithValue("@ICType", ICType);
                cmd.Parameters.AddWithValue("@firstName", firstName);
                cmd.Parameters.AddWithValue("@lastName", lastName);
                cmd.Parameters.AddWithValue("@cusType", cusType);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Customer cus = new Customer();
                    cus.cusIC = Convert.ToString(dr["cusIC"]);
                    cus.ICType = Convert.ToString(dr["ICType"]);
                    cus.firstName = Convert.ToString(dr["firstName"]);
                    cus.lastName = Convert.ToString(dr["lastName"]);
                    cus.gender = Convert.ToBoolean(dr["gender"]);
                    cus.dob = Convert.ToDateTime(dr["dob"]);
                    cus.country = Convert.ToString(dr["country"]);
                    cus.phoneNo = Convert.ToString(dr["phoneNo"]);
                    cus.email = Convert.ToString(dr["email"]);
                    cus.mailingAddress = Convert.ToString(dr["mailingAddress"]);
                    cus.postCode = Convert.ToString(dr["postCode"]);
                    cus.cusType = Convert.ToString(dr["cusType"]);
                    cusList.Add(cus);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cusList;
        }

        public static ArrayList GetAllCustomer()
        {
            ArrayList cusList = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Customer";

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Customer cus = new Customer();
                    cus.cusIC = Convert.ToString(dr["cusIC"]);
                    cus.ICType = Convert.ToString(dr["ICType"]);
                    cus.firstName = Convert.ToString(dr["firstName"]);
                    cus.lastName = Convert.ToString(dr["lastName"]);
                    cus.gender = Convert.ToBoolean(dr["gender"]);
                    cus.dob = Convert.ToDateTime(dr["dob"]);
                    cus.country = Convert.ToString(dr["country"]);
                    cus.phoneNo = Convert.ToString(dr["phoneNo"]);
                    cus.email = Convert.ToString(dr["email"]);
                    cus.mailingAddress = Convert.ToString(dr["mailingAddress"]);
                    cus.postCode = Convert.ToString(dr["postCode"]);
                    cus.cusType = Convert.ToString(dr["cusType"]);
                    cusList.Add(cus);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cusList;
        }

        public static Customer GetCusByIC(string IC)
        {
            Customer cus = new Customer();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Customer where cusIC = @cusIC";
                cmd.Parameters.AddWithValue("@cusIC", IC);

                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    cus.cusIC = Convert.ToString(dr["cusIC"]);
                    cus.ICType = Convert.ToString(dr["ICType"]);
                    cus.firstName = Convert.ToString(dr["firstName"]);
                    cus.lastName = Convert.ToString(dr["lastName"]);
                    cus.gender = Convert.ToBoolean(dr["gender"]);
                    cus.dob = Convert.ToDateTime(dr["dob"]);
                    cus.country = Convert.ToString(dr["country"]);
                    cus.phoneNo = Convert.ToString(dr["phoneNo"]);
                    cus.email = Convert.ToString(dr["email"]);
                    cus.mailingAddress = Convert.ToString(dr["mailingAddress"]);
                    cus.postCode = Convert.ToString(dr["postCode"]);
                    cus.cusType = Convert.ToString(dr["cusType"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cus;
        }

        public static Room GetRoomByID(string ID)
        {
            Room rm = new Room();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Room where roomID = @roomID";
                cmd.Parameters.AddWithValue("@roomID", ID);

                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    rm.roomID = Convert.ToInt16(dr["roomID"]);
                    rm.roomType = Convert.ToString(dr["roomType"]);
                    rm.bedType = Convert.ToString(dr["bedType"]);
                    rm.capacity = Convert.ToInt16(dr["capacity"]);
                    rm.roomRates = Convert.ToDouble(dr["roomRates"]);
                    rm.roomStatus = Convert.ToString(dr["roomStatus"]);
                    rm.remark = Convert.ToString(dr["remark"]);
                    rm.smoking = Convert.ToBoolean(dr["smoking"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rm;
        }

        public static Reservation GetReservationByNum(string resNum)
        {
            Reservation res = new Reservation();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Reservation where resNum = @resNum";
                cmd.Parameters.AddWithValue("@resNum", resNum);

                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    res.roomID = Convert.ToInt16(dr["roomID"]);
                    res.resNum = Convert.ToInt32(dr["resNum"]);
                    res.cusIC = Convert.ToString(dr["cusIC"]);
                    res.numOfAdult = Convert.ToInt16(dr["numOfAdult"]);
                    res.numOfChildren = Convert.ToInt16(dr["numOfChildren"]);
                    res.checkInDateTime = Convert.ToDateTime(dr["checkInDateTime"]);
                    res.checkOutDateTime = Convert.ToDateTime(dr["checkOutDateTime"]);
                    res.remark = Convert.ToString(dr["remark"]);
                    res.recordDateTime = Convert.ToDateTime(dr["recordDateTime"]);
                    res.resStatus = Convert.ToString(dr["resStatus"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return res;
        }

        public static int UpdateRoomStatus(string roomID, string status)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET roomStatus=@roomStatus where roomID=@roomID";
                comm.Parameters.AddWithValue("@roomStatus", status);
                comm.Parameters.AddWithValue("@roomID", roomID);

                rowsupdated = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateCustomerInfo(Customer c)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Customer SET ICType=@ICType,firstName=@firstName,lastName=@lastName,gender=@gender,dob=@dob,country=@country,phoneNo=@phoneNo,email=@email,mailingAddress=@mailingAddress,postCode=@postCode,cusType=@cusType WHERE cusIC=@cusIC";
                comm.Parameters.AddWithValue("@cusIC", c.cusIC);
                comm.Parameters.AddWithValue("@ICType", c.ICType);
                comm.Parameters.AddWithValue("@firstName", c.firstName);
                comm.Parameters.AddWithValue("@lastName", c.lastName);
                comm.Parameters.AddWithValue("@gender", c.gender);
                comm.Parameters.AddWithValue("@dob", c.dob);
                comm.Parameters.AddWithValue("@country", c.country);
                comm.Parameters.AddWithValue("@phoneNo", c.phoneNo);
                comm.Parameters.AddWithValue("@email", c.email);
                comm.Parameters.AddWithValue("@mailingAddress", c.mailingAddress);
                comm.Parameters.AddWithValue("@postCode", c.postCode);
                comm.Parameters.AddWithValue("@cusType", c.cusType);
                rowsupdated = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateReservationInfo(Reservation r)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Reservation SET cusIC=@cusIC,roomID=@roomID,numOfAdult=@numOfAdult,numOfChildren=@numOfChildren,checkInDateTime=@checkInDateTime,checkOutDateTime=@checkOutDateTime,remark=@remark,recordDateTime=@recordDateTime,resStatus=@resStatus WHERE resNum=@resNum";
                comm.Parameters.AddWithValue("@resNum", r.resNum);
                comm.Parameters.AddWithValue("@cusIC", r.cusIC);
                comm.Parameters.AddWithValue("@roomID", r.roomID);
                comm.Parameters.AddWithValue("@numOfAdult", r.numOfAdult);
                comm.Parameters.AddWithValue("@numOfChildren", r.numOfChildren);
                comm.Parameters.AddWithValue("@checkInDateTime", r.checkInDateTime);
                comm.Parameters.AddWithValue("@checkOutDateTime", r.checkOutDateTime);
                comm.Parameters.AddWithValue("@remark", r.remark);
                comm.Parameters.AddWithValue("@recordDateTime", r.recordDateTime);
                comm.Parameters.AddWithValue("@resStatus", r.resStatus);
                rowsupdated = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int InsertCustomerPayment(CustomerPayment cp)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CustomerPayment(creditCardNum,cusIC,creditHolderName,creditExpiryDate) "
                    + "VALUES(@creditCardNum,@cusIC,@creditHolderName,@creditExpiryDate)";

                comm.Parameters.AddWithValue("@creditCardNum", cp.creditCardNum);
                comm.Parameters.AddWithValue("@cusIC", cp.cusIC);
                comm.Parameters.AddWithValue("@creditHolderName", cp.creditHolderName);
                comm.Parameters.AddWithValue("@creditExpiryDate", cp.creditExpiryDate);

                rowsInserted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static int SearchPaymentByCardNo(long cardNo)//return 0 if credit card is not founded in database
        {
            int rowsFinded = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT count(*) FROM CustomerPayment WHERE creditCardNum = @creditCardNum";
                cmd.Parameters.AddWithValue("@creditCardNum", cardNo);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    rowsFinded++;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsFinded;
        }

        public static int UpdateCustomerPayment(CustomerPayment cp)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CustomerPayment SET cusIC=@cusIC, creditHolderName=@creditHolderName, creditExpiryDate=@creditExpiryDate WHERE creditCardNum=@creditCardNum";
                comm.Parameters.AddWithValue("@cusIC", cp.cusIC);
                comm.Parameters.AddWithValue("@creditHolderName", cp.creditHolderName);
                comm.Parameters.AddWithValue("@creditExpiryDate", cp.creditExpiryDate);
                comm.Parameters.AddWithValue("@creditCardNum", cp.creditCardNum);
                rowsupdated = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int InsertCustomerBill(CustomerBill cb)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CustomerBill(cusIC,roomID,roomRates,totalAmount,ifLateCheckOut) "
                    + "VALUES(@cusIC,@roomID,@roomRates,@totalAmount,@ifLateCheckOut)";

                comm.Parameters.AddWithValue("@cusIC", cb.cusIC);
                comm.Parameters.AddWithValue("@roomID", cb.roomID);
                comm.Parameters.AddWithValue("@roomRates", cb.roomRates);
                comm.Parameters.AddWithValue("@totalAmount", cb.totalAmount);
                comm.Parameters.AddWithValue("@ifLateCheckOut", cb.ifLateCheckOut);

                rowsInserted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static ArrayList SearchRoomAva(string roomType, string bedType, bool smoking, DateTime desInDT, DateTime desOutDT)
        {
            ArrayList rooms = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from Room WHERE roomType=@roomType AND bedType=@bedType AND smoking=@smoking AND roomID NOT IN" +
                    "(SELECT roomID FROM Reservation WHERE " +
                    "checkInDateTime<=@desOutDT AND resStatus = 'Reserving'" +
                    " or checkOutDateTime >= @desInDT and resStatus = 'Reserving' " +
                    "or resStatus = 'Staying') ";
                cmd.Parameters.AddWithValue("@roomType", roomType);
                cmd.Parameters.AddWithValue("@bedType", bedType);
                cmd.Parameters.AddWithValue("@smoking", smoking);
                cmd.Parameters.AddWithValue("@desInDT", desInDT);
                cmd.Parameters.AddWithValue("@desOutDT", desOutDT);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room rm = new Room();
                    rm.roomID = Convert.ToInt32(dr["roomID"]);
                    rm.roomRates = Convert.ToDouble(dr["roomRates"]);
                    rm.roomStatus = Convert.ToString(dr["roomStatus"]);
                    rm.capacity = Convert.ToInt32(dr["capacity"]);
                    rm.roomType = Convert.ToString(dr["roomType"]);
                    rm.smoking = Convert.ToBoolean(dr["smoking"]);
                    rm.remark = Convert.ToString(dr["remark"]);
                    rm.bedType = Convert.ToString(dr["bedType"]);

                    rooms.Add(rm);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rooms;
        }
    }
}
